In [1]:
import pandas as pd
import geopandas as gpd
# from jupyterthemes import jtplot
import matplotlib.pyplot as plt
import plotly.express as px
import plotly.io as pio
import basedosdados as bd
import sqlite3 as sqlite
In [2]:
pio.renderers.default='notebook'
In [3]:
dbpath = 'cities.db'
# Create connection and load spatialite extension
con = sqlite.connect(dbpath)
con.enable_load_extension(True)
con.execute('SELECT load_extension("mod_spatialite")')
# SQL must wrap the geometry in hex(st_asbinary(...))
sql = "SELECT id, sigla_uf, name, Hex(ST_AsBinary(geometry)) as geom FROM cities;"
df = gpd.GeoDataFrame.from_postgis(sql, con, geom_col="geom")
In [4]:
life_exp = bd.read_sql(
'''
SELECT id_municipio AS id, expectativa_vida
FROM basedosdados.mundo_onu_adh.municipio
WHERE ano = 2010
''',
billing_project_id='adameplayground')
In [5]:
cities_health = pd.merge(df, life_exp, how='inner', on='id')
In [6]:
rj_health = cities_health[cities_health['sigla_uf'] == 'RJ']
In [7]:
rj_health
Out[7]:
In [8]:
rj_health.plot(column ='expectativa_vida',figsize= (10,10))
Out[8]:
In [9]:
#rj_health.to_csv('rj_health.gzip', compression = 'gzip')
In [10]:
#gdf[gdf['sigla_uf']== 'RJ'].plot(figsize= (10,10),cmap='tab20b')
In [11]:
rj_health['id'] = rj_health['id'].astype(int)
rj_health
Out[11]:
In [12]:
fig = px.choropleth(rj_health,
geojson = rj_health["geom"],
locations = rj_health.index,
color = "expectativa_vida",
hover_name = "name",
hover_data = ["expectativa_vida"],#, "death_rate"],
labels = {
"expectativa_vida": "Life Expectancy"
},
color_continuous_scale = "sunsetdark",
range_color=(71, 77),
#animation_frame = 'date',
)
fig.update_geos(
projection=dict(
scale=55
),
center = dict(
lat=-22.208333,
lon=-42.896388
)
)
fig.show()
In [13]:
covid = pd.read_csv('caso_full.csv.gz', compression='gzip')
In [14]:
filt = (covid['state'] == 'RJ') & (covid['place_type'] == 'city') & (covid['is_last'])
uniq = covid[filt]['city'].isin(rj_health['name'])
rj_covid = covid[filt][uniq]
In [15]:
rj_covid['id'] = rj_covid['city_ibge_code'].astype(int)
rj_covid = rj_covid.copy()[['id', 'estimated_population', 'last_available_confirmed_per_100k_inhabitants', 'last_available_death_rate']]
rj_covid
Out[15]:
In [16]:
health_covid_rj = pd.merge(rj_health, rj_covid, how='inner', on='id')
health_covid_rj
Out[16]:
In [17]:
#health_covid_rj.to_csv('health_covid_rj.gzip', compression = 'gzip')
In [ ]:
In [18]:
fig = px.choropleth(health_covid_rj,
geojson = health_covid_rj['geom'],
locations = health_covid_rj.index,
color = "last_available_death_rate",
hover_name = "name",
hover_data = ["expectativa_vida", "last_available_death_rate"],
labels = {
"expectativa_vida": "Life Expectancy",
"last_available_death_rate": "Death Rate"
},
color_continuous_scale = "sunsetdark",
#range_color=(71, 77),
#animation_frame = 'date',
)
fig.update_geos(
projection=dict(
scale=55
),
center = dict(
lat=-22.208333,
lon=-42.896388
)
)
fig.show()
In [19]:
fig = px.scatter(health_covid_rj, x="last_available_death_rate", y="expectativa_vida", hover_data=['name'], trendline="ols")
fig.show()
In [27]:
!jupyter nbconvert health_analysis.ipynb --to html --template classic